import sqlite3
from sqlite3 import Error
import os

def connect_db():
    """创建交易记录数据库连接"""
    db_path = os.path.join(os.path.dirname(__file__), "trade_monitor.db")
    
    conn = None
    try:
        conn = sqlite3.connect(db_path)
        # 检查表是否已存在
        cur = conn.cursor()
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='trade_records'")
        if not cur.fetchone():
            conn.execute('''
                CREATE TABLE trade_records
                (id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 主键ID，自增长
                product_name TEXT NOT NULL,            -- 品种名称
                exchange_code TEXT NOT NULL,           -- 交易所代码
                direction TEXT NOT NULL,               -- 交易方向: 买入/卖出
                action_type TEXT NOT NULL,             -- 操作类型: 开仓/平仓
                volume INTEGER NOT NULL,               -- 交易手数
                price REAL NOT NULL,                   -- 成交价格
                remark TEXT,                           -- 备注信息，交易原因
                trade_time TEXT DEFAULT CURRENT_TIMESTAMP)  -- 交易时间，默认当前时间
                ''')
        return conn
    except Error as e:
        print(f"数据库连接错误: {e}")
        return None

def insert_trade_record(conn, record):
    """添加交易记录"""
    sql = '''INSERT INTO trade_records(
                product_name, exchange_code,
                direction, action_type, volume, 
                price, remark)
             VALUES(?,?,?,?,?,?,?)'''
    cur = conn.cursor()
    try:
        cur.execute(sql, record)
        conn.commit()
        return cur.lastrowid
    except Error as e:
        print(f"添加交易记录错误: {e}")
        return None

def get_trade_records(conn, product_name=None, limit=100):
    """获取交易记录"""
    cur = conn.cursor()
    try:
        if product_name:
            cur.execute('''SELECT * FROM trade_records 
                        WHERE product_name=? 
                        ORDER BY trade_time DESC LIMIT ?''', 
                       (product_name, limit))
        else:
            cur.execute('''SELECT * FROM trade_records 
                        ORDER BY trade_time DESC LIMIT ?''', (limit,))
        return cur.fetchall()
    except Error as e:
        print(f"获取交易记录错误: {e}")
        return None

def delete_trade_record(conn, record_id):
    """删除交易记录"""
    sql = 'DELETE FROM trade_records WHERE id=?'
    cur = conn.cursor()
    try:
        cur.execute(sql, (record_id,))
        conn.commit()
        return cur.rowcount
    except Error as e:
        print(f"删除交易记录错误: {e}")
        return None

if __name__ == '__main__':
    # 测试代码
    conn = connect_db()
    if conn:
        # 测试插入记录
        test_record = (
            "螺纹钢2501", "SHFE.rb2501",
            "买入", "开仓", 5,
            3500.0, "测试记录"
        )
        insert_trade_record(conn, test_record)
        
        # 查询记录
        records = get_trade_records(conn)
        print("最近交易记录:")
        for r in records:
            print(r)
            
        conn.close()
